/**
 * 
 */
package com.ph.irp.base.assistantmaterial.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.assistantmaterial.dao.PBaseAssistantmaterialIndustryDao;
import com.ph.irp.model.PBaseAssistantmaterialIndustry;
import com.ph.irp.model.PBaseAssistantmaterialSuppliercategory;
import com.ph.util.DBUtil;

/**
 * @author wgb
 * 
 */
public class PBaseAssistantmaterialIndustryDaoImpl implements
		PBaseAssistantmaterialIndustryDao {
	private ComboPooledDataSource dataSource;
	private List<PBaseAssistantmaterialIndustry> pBaseAssistantmaterialIndustryList = null;
	private PBaseAssistantmaterialIndustry pbaseAssistantmaterialIndustry = null;

	/**
	 * 增加辅助资料
	 * 
	 */
	@Override
	public void addPBaseAssistantmaterialIndustry(
			PBaseAssistantmaterialIndustry baseAssistantmaterialIndustry) throws Throwable{
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		// ResultSet resultSet = null;
		StringBuilder sb = new StringBuilder();
		sb.append("insert into p_base_assistantmaterial_industry(IndustryCode,IndustryName) values(?,?)");
		try {
			connection = this.dataSource.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setString(1, baseAssistantmaterialIndustry.getIndustryCode().trim());
			preparedStatement.setString(2, baseAssistantmaterialIndustry.getIndustryName().trim());
			preparedStatement.execute();
		} 
		catch(Exception e){
			throw new Exception(e.getMessage());
			
		}
		finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, null);
			} catch (Exception e) {

				throw new Exception(e.getMessage());
			}
		}
	}

	/**
	 * 根据id数组删除辅助资料行业信息
	 */
	@Override
	public void deletePBaseAssistantmaterialIndustryById(Object[] IndustryIDs) throws Throwable{
		// int result = 0;
		int loop = 0;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		// ResultSet resultSet = null;
		
		//StringBuilder sb = new StringBuilder();
		//sb.append("delete from p_base_assistantmaterial_industry where IndustryId=?");
		try {
			connection = this.dataSource.getConnection();
			for (loop = 0; loop < IndustryIDs.length; loop++){
						preparedStatement = connection.prepareStatement("delete from p_base_assistantmaterial_industry where IndustryId=?");
						int IndustryID = Integer.valueOf((String) IndustryIDs[loop]);
						preparedStatement.setInt(1, IndustryID);
						preparedStatement.execute();
			  }
		} 
		catch(Exception e){
			throw new Exception(e.getMessage());
		}
		finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, null);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}

		}
	}

	/**
	 * 得到所有辅助资料行业信息
	 */
	@Override
	public List<PBaseAssistantmaterialIndustry> getAllPBaseAssistantmaterialIndustry() throws Throwable{
		pBaseAssistantmaterialIndustryList = new ArrayList<PBaseAssistantmaterialIndustry>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		StringBuilder sb = new StringBuilder();
		sb.append("select IndustryID,IndustryCode,IndustryName from p_base_assistantmaterial_industry ");
		try {
			connection = this.getDataSource().getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			resultSet = preparedStatement.executeQuery();
			this.turnResultSetToList(pBaseAssistantmaterialIndustryList,
					resultSet);
			// pbaseAssistantmaterialIndustry =
			// pBaseAssistantmaterialIndustryList.get(0);

		} 
		catch(Exception e){
			throw new Exception(e.getMessage());
		}
		finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, null);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}
		return pBaseAssistantmaterialIndustryList;
	}

	/**
	 * 根据行业代码得到行业list
	 */
	@Override
	public List<PBaseAssistantmaterialIndustry> getPBaseAssistantmaterialIndustryByCode(
			String IndustryCode) throws Throwable{
		pBaseAssistantmaterialIndustryList = new ArrayList<PBaseAssistantmaterialIndustry>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		StringBuilder sb = new StringBuilder();
		sb.append("select IndustryID,IndustryCode,IndustryName from p_base_assistantmaterial_industry where IndustryCode like ?");
		try {
			connection = this.getDataSource().getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setString(1, "%" + IndustryCode + "%");
			resultSet = preparedStatement.executeQuery();
			this.turnResultSetToList(pBaseAssistantmaterialIndustryList,
					resultSet);
			// pbaseAssistantmaterialIndustry =
			// pBaseAssistantmaterialIndustryList.get(0);

		} 
		catch(Exception e){
			throw new Exception(e.getMessage());
		}
		finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, null);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}
		return pBaseAssistantmaterialIndustryList;
	}

	/**
	 * 根据代码和名称得到辅助资料行业信息
	 */
	@Override
	public List<PBaseAssistantmaterialIndustry> getPBaseAssistantmaterialIndustryByCodeAndName(
			String IndustryCode, String IndustryName) throws Throwable{
		pBaseAssistantmaterialIndustryList = new ArrayList<PBaseAssistantmaterialIndustry>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		StringBuilder sb = new StringBuilder();
		sb.append("select IndustryID,IndustryCode,IndustryName from p_base_assistantmaterial_industry where IndustryCode like ? and IndustryName like ?");
		try {
			connection = this.getDataSource().getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setString(1, "%" + IndustryCode + "%");
			preparedStatement.setString(2, "%" + IndustryName + "%");
			resultSet = preparedStatement.executeQuery();
			this.turnResultSetToList(pBaseAssistantmaterialIndustryList,
					resultSet);
			// pbaseAssistantmaterialIndustry =
			// pBaseAssistantmaterialIndustryList.get(0);

		} 
		catch(Exception e){
			throw new Exception(e.getMessage());
		}
		finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, null);
			} catch (Exception ex) {
		throw new Exception(ex.getMessage());
			}
		}
		return pBaseAssistantmaterialIndustryList;
	}

	/**
	 * 根据Id得到行业信息
	 * 
	 */
	@Override
	public PBaseAssistantmaterialIndustry getPBaseAssistantmaterialIndustryById(
			Integer IndustryID) throws Throwable{

		pBaseAssistantmaterialIndustryList = new ArrayList<PBaseAssistantmaterialIndustry>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		StringBuilder sb = new StringBuilder();
		sb.append("select IndustryID,IndustryCode,IndustryName from p_base_assistantmaterial_industry where IndustryID=?");
		try {
			connection = this.getDataSource().getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setInt(1, IndustryID);
			resultSet = preparedStatement.executeQuery();
			this.turnResultSetToList(pBaseAssistantmaterialIndustryList,
					resultSet);
			pbaseAssistantmaterialIndustry = pBaseAssistantmaterialIndustryList
					.get(0);

		}
		catch(Exception e ){
			throw new Exception(e.getMessage());
		}
		 finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, null);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}
		return pbaseAssistantmaterialIndustry;
	}

	/**
	 * 根据辅助资料名称查询辅助资料行业信息
	 * 
	 */
	@Override
	public List<PBaseAssistantmaterialIndustry> getPBaseAssistantmaterialIndustryByName(
			String IndustryName) throws Throwable{
		pBaseAssistantmaterialIndustryList = new ArrayList<PBaseAssistantmaterialIndustry>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		StringBuilder sb = new StringBuilder();
		sb
				.append("select IndustryID,IndustryCode,IndustryName from p_base_assistantmaterial_industry where IndustryName like ?");
		try {
			connection = this.getDataSource().getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setString(1, "%" + IndustryName + "%");
			resultSet = preparedStatement.executeQuery();
			this.turnResultSetToList(pBaseAssistantmaterialIndustryList,
					resultSet);
			// pbaseAssistantmaterialIndustry =
			// pBaseAssistantmaterialIndustryList.get(0);

		} 
		catch(Exception e){
			throw new Exception(e.getMessage());
		}
		finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, null);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}
		return pBaseAssistantmaterialIndustryList;
	}

	/**
	 * 更新辅助资料行业信息
	 */
	@Override
	public void updatePBaseAssistantmaterialIndustry(
			PBaseAssistantmaterialIndustry baseAssistantmaterialIndustry) throws Throwable{
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		StringBuilder sb = new StringBuilder();
		sb.append("update p_base_assistantmaterial_industry set IndustryCode=?,IndustryName=? where IndustryID=?");
		try {
			connection = this.getDataSource().getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setString(1, baseAssistantmaterialIndustry.getIndustryCode());
			preparedStatement.setString(2, baseAssistantmaterialIndustry.getIndustryName());
			preparedStatement.setInt(3, baseAssistantmaterialIndustry.getIndustryId());
			preparedStatement.execute();
		} 
		catch(Exception e){
			throw new Exception(e.getMessage());
		}
		finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, null);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}

	}

	/**
	 * @return the dataSource
	 */
	public ComboPooledDataSource getDataSource() {
		return dataSource;
	}

	/**
	 * @param dataSource
	 *            the dataSource to set
	 */
	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	public List<PBaseAssistantmaterialIndustry> turnResultSetToList(
			List<PBaseAssistantmaterialIndustry> assistantmaterialIndustryList,
			ResultSet resultSet) throws Throwable {

		while (resultSet.next()) {

			PBaseAssistantmaterialIndustry assistantmaterialIndustry = new PBaseAssistantmaterialIndustry();

			assistantmaterialIndustry.setIndustryId(resultSet.getInt("IndustryID"));
			assistantmaterialIndustry.setIndustryCode(resultSet.getString("IndustryCode"));
			assistantmaterialIndustry.setIndustryName(resultSet.getString("IndustryName"));
			assistantmaterialIndustryList.add(assistantmaterialIndustry);

		}
		return assistantmaterialIndustryList;
	}
	/**
	 * 查看是否存在相同的代码
	 * @param supplierCategoryCode
	 * @return
	 */
	public boolean isSameCode(String supplierCategoryCode)throws Throwable{
		
		pBaseAssistantmaterialIndustryList = new ArrayList<PBaseAssistantmaterialIndustry>();
		Connection connection = null;
		ResultSet resultSet = null;
		PreparedStatement preparedStatement = null;
		StringBuilder sb = new StringBuilder();
		sb.append("select IndustryID,IndustryCode,IndustryName from p_base_assistantmaterial_industry where IndustryCode = ?");
		try{
			connection = this.dataSource.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setString(1, supplierCategoryCode);
			resultSet = preparedStatement.executeQuery();
			if(resultSet.next()){
				return true;
			}
			else{
				return false;
			}
			
		}
		catch(Exception e){
			throw new Exception(e.getMessage());
			
		}
		finally{
			try {
				DBUtil.setNullObject(connection, preparedStatement, null);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}
		
	}
	/**
	 * 查看是否存在相同的名称
	 * @param supplierCategoryName
	 * @return
	 */
	public boolean isSameName(String supplierCategoryName)throws Throwable{
		
		pBaseAssistantmaterialIndustryList = new ArrayList<PBaseAssistantmaterialIndustry>();
		Connection connection = null;
		ResultSet resultSet = null;
		PreparedStatement preparedStatement = null;
		StringBuilder sb = new StringBuilder();
		sb.append("select IndustryID,IndustryCode,IndustryName from p_base_assistantmaterial_industry where IndustryName = ?");
		try{
			connection = this.dataSource.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setString(1, supplierCategoryName);
			resultSet = preparedStatement.executeQuery();
			if(resultSet.next()){
				return true;
			}
			else{
				return false;
			}
			
		}
		catch(Exception e){
			throw new Exception(e.getMessage());
			
		}
		finally{
			try {
				DBUtil.setNullObject(connection, preparedStatement, null);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}
		
	}

}
